#include "sqlite.h"

Sqlite::Sqlite()
{


}

void Sqlite::Open()
{
    //添加MYSQL数据库
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE"); //数据库对象
    //设置数据库
    db.setDatabaseName("./library.db");
    //打开数据库
    if( !db.open() )
    {
        //数据库打开失败
        welog_debug() << "db open fail";
        return ;
    }
    QSqlQuery query;
    bool ret = query.exec("create table  if not exists book (book QString, writer QString, \
                                    num int, sell int)");
    if(!ret)
    {
        welog_debug() << "create fail book:" << query.lastError().text();
    }

    ret = query.exec("create table  if not exists lend (id QString, user QString, phone QString,\
                                    book QString, sell int, lend QString, return QString)");
    if(!ret)
    {
        welog_debug() << "create fail lend : " << query.lastError().text();
    }

    ret = query.exec("create table  if not exists user (id QString, user QString, \
                                    phone QString, passwd QString, money int, option QString)");
    if(!ret)
    {
        welog_debug() << "create fail user:" << query.lastError().text();
    }
}

void Sqlite::Insert_user(QString name, QString id, QString phone, QString passwd, int money, QString order)
{
    QString str = QString("insert into user(id, user, phone, passwd, money, option) \
                        values('%1', '%2', '%3', '%4', '%5', '%6')").arg(id).arg(name).arg(phone).arg(passwd).arg(money).arg(order);
    QSqlQuery query;
    bool ret = query.exec(str);
    if(!ret)
    {
        welog_debug() << "insert fail:" << query.lastError().text();
    }
}

User_t Sqlite::Select_user(QString id, QString passwd)
{
    User_t user;
    QString select = QString("select * from user where id = '%1' and passwd = '%2'").arg(id).arg(passwd);
    welog_debug() << select;
    QSqlQuery query;
    query.exec(select);
    while(query.next())
    {
        welog_debug() << "select ok";
        user.id = query.value(0).toString();
        user.name = query.value(1).toString();
        user.phone = query.value(2).toString();
        user.passwd = query.value(3).toString();
        user.money = query.value(4).toInt();
        break;
    }

    return user;
}

User_t Sqlite::Select_user(QString id)
{
    User_t user;
    QString select = QString("select * from user where id = '%1'").arg(id);
    welog_debug() << select;
    QSqlQuery query;
    query.exec(select);
    while(query.next())
    {
        welog_debug() << "select ok";
        user.id = query.value(0).toString();
        user.name = query.value(1).toString();
        user.phone = query.value(2).toString();
        user.passwd = query.value(3).toString();
        user.money = query.value(4).toInt();
        user.option = query.value(5).toString();
        break;
    }

    return user;
}

void Sqlite::Update_userMoney(User_t user)
{
    QString str = QString("update user set money = %1  where id = '%2'").arg(user.money).arg(user.id);
    welog_debug() << str;
    QSqlQuery query;
    bool ret = query.exec(str);
    if(!ret)
    {
        welog_debug() << "update fail:" << query.lastError().text();
    }
}

void Sqlite::Update_userOrder(QString id, QString order)
{
    QJsonArray array;
    QString data = Select_userOrder(id);
    if(data.simplified().isEmpty())
    {
        welog_debug() << "data empty";
        QJsonObject obj;
        obj.insert("option", order);
        array.insert(0, QJsonValue(obj));
    }
    else
    {
        welog_debug() << data;
        QJsonParseError err;
        QJsonDocument doc = QJsonDocument::fromJson(data.toUtf8(), &err);
        if(err.error != QJsonParseError::NoError)
        {
            welog_debug() << "fromJson fail" << err.error;
            return;
        }

        QJsonObject obj = doc.object();
        QJsonValue value = obj.value("orderList");
        if(value.isArray())
        {

            QJsonArray arr = value.toArray();

            QJsonObject obj;
            obj.insert("option", order);
            welog_debug() << "array : " << arr.size();
            arr.insert(arr.size(), QJsonValue(obj));

            array = arr;
        }
    }

    QJsonObject json;
    json.insert("orderList", QJsonValue(array));

    QJsonDocument document;
    document.setObject(json);
    QString str = document.toJson(QJsonDocument::Compact);
    if(!str.isEmpty())
    {
        QString cmd = QString("update user set option = '%1'  where id = '%2'").arg(str).arg(id);
        welog_debug() << cmd;
        QSqlQuery query;
        bool ret = query.exec(cmd);
        if(!ret)
        {
            welog_debug() << "update fail:" << query.lastError().text();
        }
    }
}

QString Sqlite::Select_userOrder(QString id)
{
    QString ret = "";
    QString select = QString("select * from user where id = '%1'").arg(id);
    //welog_debug() << select;
    QSqlQuery query;
    query.exec(select);
    while(query.next())
    {
        ret = query.value(5).toString();
    }
    return ret;
}


QList<Book_t>  Sqlite::Select_book()
{
    QList<Book_t>  list;
    QString select = QString("select * from book");
    //welog_debug() << select;
    QSqlQuery query;
    query.exec(select);
    while(query.next())
    {
        Book_t book;
        book.name = query.value(0).toString();
        book.img = query.value(1).toString();
        book.num = query.value(2).toInt();
        book.sell = query.value(3).toInt();
        list.push_back(book);
        //break;
    }

    return list;
}

Book_t Sqlite::Select_book(QString name)
{
    Book_t book;
    QString select = QString("select * from book where book = '%1'").arg(name);
    //welog_debug() << select;
    QSqlQuery query;
    query.exec(select);
    while(query.next())
    {
        book.name = query.value(0).toString();
        book.img = query.value(1).toString();
        book.num = query.value(2).toInt();
        book.sell = query.value(3).toInt();
        //break;
    }

    return book;
}

void Sqlite::Update_bookNum(QString name, int num)
{
    QString str = QString("update book set num = %1  where book = '%2'").arg(num).arg(name);
    welog_debug() << str;
    QSqlQuery query;
    bool ret = query.exec(str);
    if(!ret)
    {
        welog_debug() << "update fail:" << query.lastError().text();
    }
}

void Sqlite::Update_bookNum(QString name)
{
    QList<Book_t> list = Select_book();
    if(list.size())
    {
        for(int i =0; i<list.size(); i++)
        {
            if(list.at(i).name == name)
            {
                QString str = QString("update book set num = %1  where book = '%2'").arg(list.at(i).num+1).arg(name);
                welog_debug() << str;
                QSqlQuery query;
                bool ret = query.exec(str);
                if(!ret)
                {
                    welog_debug() << "update fail:" << query.lastError().text();
                }
                break;
            }
        }
    }
}

void Sqlite::Update_book(Book_t book)
{
    QString str = QString("update book set num = %1 , sell = %2 where book = '%3'").arg(book.num).arg(book.sell).arg(book.name);
    welog_debug() << str;
    QSqlQuery query;
    bool ret = query.exec(str);
    if(!ret)
    {
        welog_debug() << "update fail:" << query.lastError().text();
    }
}

void Sqlite::Insert_book(Book_t book)
{
    QString str = QString("insert into book(book, writer, num, sell) \
                values('%1', '%2', '%3', '%4')").arg(book.name)\
                .arg(book.img).arg(book.num).arg(book.sell);
    QSqlQuery query;
    bool ret = query.exec(str);
    if(!ret)
    {
        welog_debug() << "insert fail:" << query.lastError().text();
    }
}

void Sqlite::Delete_book(QString name)
{
    QString str = QString("DELETE FROM book WHERE  book = '%1'").arg(name);
    welog_debug() << str;
    QSqlQuery query;
    query.exec(str);
}

void Sqlite::Insert_lend(User_t user, QString book, int sell)
{
    QDateTime timeDate = QDateTime::currentDateTime();  // 获取当前时间
    uint timeStr = timeDate .toTime_t();   				// 将当前时间转为时间戳

    uint time = timeStr + 5*60*1000;

    QString str = QString("insert into lend(id, user, phone, book, sell, lend, return) \
                values('%1', '%2', '%3', '%4', %5, '%6', '%7')").arg(user.id)\
                .arg(user.name).arg(user.phone).arg(book).arg(sell).arg(timeStr).arg(time);
    QSqlQuery query;
    bool ret = query.exec(str);
    if(!ret)
    {
        welog_debug() << "insert fail:" << query.lastError().text();
    }
}

QList<Lend_t> Sqlite::Select_lend(QString id)
{
    QList<Lend_t>  list;
    QString select = QString("select * from lend where id = '%1'").arg(id);
    //welog_debug() << select;
    QSqlQuery query;
    query.exec(select);
    while(query.next())
    {
        Lend_t item;
        item.id = query.value(0).toString();
        item.user = query.value(1).toString();
        item.phone = query.value(2).toString();
        item.book = query.value(3).toString();
        item.sell = query.value(4).toInt();
        item.lend = query.value(5).toString();
        item.retun = query.value(6).toString();
        list.push_back(item);
        //break;
    }

    return list;
}

QList<Lend_t> Sqlite::Select_lend()
{
    QList<Lend_t>  list;
    QString select = QString("select * from lend");
    //welog_debug() << select;
    QSqlQuery query;
    query.exec(select);
    while(query.next())
    {
        Lend_t item;
        item.id = query.value(0).toString();
        item.user = query.value(1).toString();
        item.phone = query.value(2).toString();
        item.book = query.value(3).toString();
        item.sell = query.value(4).toInt();
        item.lend = query.value(5).toString();
        item.retun = query.value(6).toString();
        list.push_back(item);
        //break;
    }

    return list;
}

void Sqlite::Delete_lend(QString id, QString book, QString lend)
{
    QString str = QString("DELETE FROM lend WHERE  id = '%1' and book = '%2' and lend='%3'").arg(id).arg(book).arg(lend);
    welog_debug() << str;
    QSqlQuery query;
    query.exec(str);
}










